﻿using QuanLyGiaoVuDAL.ADOWrapper;
using QuanLyGiaoVuDAL.Extensions;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;

namespace QuanLyGiaoVuDAL.Repositories.DatabaseRepositories
{
    public class FileRepository : IFileRepository
    {
        private SqlConnection GetConnection()
        {
            SqlConnection conn = StoreProcedureExecutor.GetConnection();
            conn.Open();
            return conn;
        }

        public bool GetFileStreamById(Guid fileId, out Stream fileStream, out String fileName, out String contentType)
        {
            SqlConnection conn = GetConnection();
            try
            {
                SqlCommand cmd = new SqlCommand(
                    @"SELECT DATALENGTH (NoiDungTapTin) as content_length, TenTapTin, KieuNoiDung, NoiDungTapTin
                FROM [dbo].[TAPTIN]
                WHERE MaTapTin = @MaTapTin;", conn);
                SqlParameter paramFilename = DBParameterFactory.Create("MaTapTin", fileId);
                cmd.Parameters.Add(paramFilename);
                SqlDataReader reader = cmd.ExecuteReader(
                    CommandBehavior.SequentialAccess |
                    CommandBehavior.SingleResult |
                    CommandBehavior.SingleRow |
                    CommandBehavior.CloseConnection);
                if (false == reader.Read())
                {
                    reader.Dispose();
                    conn = null;
                    fileStream = null;
                    fileName = null;
                    contentType = String.Empty;
                    return false;
                }

                long contentLength = reader.GetInt64(0);
                fileName = reader.GetString(1);
                contentType = reader.GetString(2);
                fileStream = new SqlReaderStream(reader, 3);
                conn = null;
                return true;
            }
            finally
            {
                if (null != conn)
                {
                    conn.Dispose();
                }
            }
        }

        public bool SaveFileFromStream(Guid? fileId, Stream dataStream, String fileName, String contentType, out Guid newFileId)
        {
            try
            {
                newFileId = fileId ?? Guid.NewGuid();
                using (SqlConnection conn = GetConnection())
                {
                    using (SqlTransaction trn = conn.BeginTransaction())
                    {
                        SqlCommand cmdInsert = new SqlCommand(
                            @"INSERT INTO TAPTIN (
                        MaTapTin, TenTapTin, NoiDungTapTin, KieuNoiDung )
                    values (
                        @MaTapTin,
                        @TenTapTin,
                        @data,
                        @contentType
                        );", conn, trn);
                        cmdInsert.Parameters.Add("@data", SqlDbType.VarBinary, -1);
                        cmdInsert.Parameters.Add("@MaTapTin", SqlDbType.UniqueIdentifier);
                        cmdInsert.Parameters["@MaTapTin"].Value = newFileId;
                        cmdInsert.Parameters.Add("@TenTapTin", SqlDbType.VarChar, 100);
                        cmdInsert.Parameters["@TenTapTin"].Value = fileName;
                        cmdInsert.Parameters.Add("@contentType", SqlDbType.VarChar,50);
                        cmdInsert.Parameters["@contentType"].Value = contentType;

                        SqlCommand cmdUpdate = new SqlCommand(
                                @"UPDATE TAPTIN
                        SET NoiDungTapTin .write (@data, NULL, NULL)
                        WHERE MaTapTin = @MaTapTin;", conn, trn);
                        cmdUpdate.Parameters.Add("@data", SqlDbType.VarBinary, -1);
                        cmdUpdate.Parameters.Add("@MaTapTin", SqlDbType.UniqueIdentifier);
                        cmdUpdate.Parameters["@MaTapTin"].Value = newFileId;

                        using (Stream uploadStream = new BufferedStream(
                            new SqlStreamUpload
                            {
                                InsertCommand = fileId == null ? cmdInsert : null,
                                UpdateCommand = cmdUpdate,
                                InsertDataParam = cmdInsert.Parameters["@data"],
                                UpdateDataParam = cmdUpdate.Parameters["@data"]
                            }, 8040))
                        {
                            CopyStream(dataStream,uploadStream,4096);
                        }
                        trn.Commit();
                    }
                }
            }
            catch (Exception exception)
            {
                newFileId = Guid.Empty;
                return false;
            }
            return true;
        }

        public bool DeleteFileById(Guid fileId)
        {
            try
            {
                var dbResult = StoreProcedureExecutor.ExecuteStoreProcedure("[dbo].[usp_TAPTINDelete]", DBParameterFactory.Create("@MaTapTin", fileId));
                return true;
            }
            catch (Exception ex)
            {
                return false;
            }
        }

        private void CopyStream(Stream sourceStream, Stream destination, int bufferSize)
        {
            int num;
            byte[] buffer = new byte[bufferSize];
            while ((num = sourceStream.Read(buffer, 0, buffer.Length)) != 0)
            {
                destination.Write(buffer, 0, num);
            }
        }
    }
}
